/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.surveilans.model;

import com.surveilans.classes.DataSurveilans;
import com.surveilans.classes.Kabupaten;
import com.surveilans.classes.Penyakit;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Yudi Retanto
 */
public class DataModel extends Model {
    
    public DataModel() {
        super();
    }
    
    public ArrayList<Penyakit> getPenyakit() {
        ArrayList<Penyakit> ap = new ArrayList<Penyakit>();
        
        Statement stmt = null;
        try {
            stmt = con.createStatement();
            String query="SELECT * FROM penyakit";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                Penyakit p = new Penyakit(rs.getInt("id"), rs.getString("nama"));
                ap.add(p);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ap;
    }
    
    public int getIdPenyakit(String kode){
        int id = 0;
        
        Statement stmt = null;
        try {
            stmt = con.createStatement();
            String query="SELECT id from penyakit WHERE kode='"+kode+"'";
            ResultSet rs = stmt.executeQuery(query);

            rs.next();
            id=rs.getInt("id");
        } catch (SQLException ex) {
            Logger.getLogger(DataModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return id;
    }
    
    

    int getIdKabupaten(String kode) {
        int id = 0;
        
        Statement stmt = null;
        try {
            stmt = con.createStatement();
            String query="SELECT id from kabupaten WHERE kode='"+kode+"'";
            ResultSet rs = stmt.executeQuery(query);

            rs.next();
            id=rs.getInt("id");
        } catch (SQLException ex) {
            Logger.getLogger(DataModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return id;
    }

    public void addData(ArrayList<DataSurveilans> ad) {
        Iterator itr = ad.iterator();
        while (itr.hasNext()) {
            this.addData((DataSurveilans)itr.next());
        }
    }
    
    public void addData(DataSurveilans dp){
        PreparedStatement stmt = null;
        try {
            SimpleDateFormat sdfd = new SimpleDateFormat("yyyy-MM-dd");
            String date = sdfd.format(dp.getTanggal());
            String query = "INSERT INTO data_surveilans "
                    + "(id_unit,id_penyakit,tanggal,range1,range2,range3,range4,range5,range6,range7,range8,range9,range10,range11,range12) "
                    + "VALUES ("+dp.getId_unit()+","+dp.getId_penyakit()+",'"+date+"',"+dp.getRange1()+","+dp.getRange2()+","+dp.getRange3()+","+dp.getRange4()+","+dp.getRange5()+","+dp.getRange6()+","+dp.getRange7()+","+dp.getRange8()+","+dp.getRange9()+","+dp.getRange10()+","+dp.getRange11()+","+dp.getRange12()+")";
            stmt = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            stmt.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DataModel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public ArrayList<DataSurveilans> getDataPenyakit() {
        ArrayList<DataSurveilans> ad = new ArrayList<DataSurveilans>();
        
        Statement stmt = null;
        try {
            stmt = con.createStatement();
            String query="SELECT * FROM data_surveilans "
                    + "LEFT JOIN penyakit ON data_surveilans.id_penyakit=penyakit.id "
                    + "ORDER BY tanggal,id_unit";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                DataSurveilans dp = new DataSurveilans();
                dp.setId_unit(rs.getInt("data_surveilans.id_unit"));
                dp.setId(rs.getInt("data_surveilans.id"));
                dp.setId_penyakit(rs.getInt("id_penyakit"));
                dp.setNama_penyakit(rs.getString("penyakit.nama"));
                DateFormat dfm = new SimpleDateFormat("yyyy-MM-dd");
                Date a = null;
                try {
                    a = dfm.parse(rs.getString("tanggal"));
                } catch (ParseException ex) {
                    Logger.getLogger(DataModel.class.getName()).log(Level.SEVERE, null, ex);
                }
                dp.setTanggal(a);
                dp.setTanggal_str(rs.getString("tanggal"));
                dp.setRange1(rs.getInt("range1"));
                dp.setRange2(rs.getInt("range2"));
                dp.setRange3(rs.getInt("range3"));
                dp.setRange4(rs.getInt("range4"));
                dp.setRange5(rs.getInt("range5"));
                dp.setRange6(rs.getInt("range6"));
                dp.setRange7(rs.getInt("range7"));
                dp.setRange8(rs.getInt("range8"));
                dp.setRange9(rs.getInt("range9"));
                dp.setRange10(rs.getInt("range10"));
                dp.setRange11(rs.getInt("range11"));
                dp.setRange12(rs.getInt("range12"));
                dp.updateTotal();
                if(dp.getTotal() > 0){
                    ad.add(dp);
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ad;
    }

    public ArrayList<DataSurveilans> getDataPenyakit(int unit_id) {
        ArrayList<DataSurveilans> ad = new ArrayList<DataSurveilans>();
        
        Statement stmt = null;
        try {
            stmt = con.createStatement();
            String query="SELECT * FROM data_surveilans "
                    + "LEFT JOIN penyakit ON data_surveilans.id_penyakit=penyakit.id "
                    + "WHERE id_unit="+unit_id+" "
                    + "ORDER BY tanggal DESC";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                DataSurveilans dp = new DataSurveilans();
                dp.setId(rs.getInt("data_surveilans.id"));
                dp.setId_penyakit(rs.getInt("id_penyakit"));
                dp.setNama_penyakit(rs.getString("penyakit.nama"));
                DateFormat dfm = new SimpleDateFormat("yyyy-MM-dd");
                Date a = null;
                try {
                    a = dfm.parse(rs.getString("tanggal"));
                } catch (ParseException ex) {
                    Logger.getLogger(DataModel.class.getName()).log(Level.SEVERE, null, ex);
                }
                dp.setTanggal(a);
                dp.setTanggal_str(rs.getString("tanggal"));
                dp.setRange1(rs.getInt("range1"));
                dp.setRange2(rs.getInt("range2"));
                dp.setRange3(rs.getInt("range3"));
                dp.setRange4(rs.getInt("range4"));
                dp.setRange5(rs.getInt("range5"));
                dp.setRange6(rs.getInt("range6"));
                dp.setRange7(rs.getInt("range7"));
                dp.setRange8(rs.getInt("range8"));
                dp.setRange9(rs.getInt("range9"));
                dp.setRange10(rs.getInt("range10"));
                dp.setRange11(rs.getInt("range11"));
                dp.setRange12(rs.getInt("range12"));
                dp.updateTotal();
                ad.add(dp);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ad;
    }

    public void deleteDataSurveilans(int id_data) {
        PreparedStatement stmt = null;
        try {
            String query = "DELETE FROM data_surveilans "
                    + "WHERE id="+id_data;
            stmt = con.prepareStatement(query,Statement.RETURN_GENERATED_KEYS);
            stmt.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DataModel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public DataSurveilans getDataSurveilans(int id_data) {
        DataSurveilans ds = new DataSurveilans();
        
        Statement stmt = null;
        try {
            stmt = con.createStatement();
            String query="SELECT * FROM data_surveilans "
                    + "LEFT JOIN penyakit ON data_surveilans.id_penyakit=penyakit.id "
                    + "WHERE data_surveilans.id="+id_data;
            ResultSet rs = stmt.executeQuery(query);
            rs.next();
            ds.setId(rs.getInt("data_surveilans.id"));
            ds.setId_unit(rs.getInt("data_surveilans.id_unit"));
            ds.setId_penyakit(rs.getInt("id_penyakit"));
            ds.setNama_penyakit(rs.getString("penyakit.nama"));
            DateFormat dfm = new SimpleDateFormat("yyyy-MM-dd");
            Date a = null;
            try {
                a = dfm.parse(rs.getString("tanggal"));
            } catch (ParseException ex) {
                Logger.getLogger(DataModel.class.getName()).log(Level.SEVERE, null, ex);
            }
            ds.setTanggal(a);
            ds.setTanggal_str(rs.getString("tanggal"));
            ds.setRange1(rs.getInt("range1"));
            ds.setRange2(rs.getInt("range2"));
            ds.setRange3(rs.getInt("range3"));
            ds.setRange4(rs.getInt("range4"));
            ds.setRange5(rs.getInt("range5"));
            ds.setRange6(rs.getInt("range6"));
            ds.setRange7(rs.getInt("range7"));
            ds.setRange8(rs.getInt("range8"));
            ds.setRange9(rs.getInt("range9"));
            ds.setRange10(rs.getInt("range10"));
            ds.setRange11(rs.getInt("range11"));
            ds.setRange12(rs.getInt("range12"));
            ds.updateTotal();
        } catch (SQLException ex) {
            Logger.getLogger(DataModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ds;
    }

    public void updateDataSurveilans(DataSurveilans ds) {
        PreparedStatement stmt = null;
        try {
            String query = "UPDATE data_surveilans "
                    + "SET range1="+ds.getRange1()+","
                    + "range2="+ds.getRange2()+","
                    + "range3="+ds.getRange3()+","
                    + "range4="+ds.getRange4()+","
                    + "range5="+ds.getRange5()+","
                    + "range6="+ds.getRange6()+","
                    + "range7="+ds.getRange7()+","
                    + "range8="+ds.getRange8()+","
                    + "range9="+ds.getRange9()+","
                    + "range10="+ds.getRange10()+","
                    + "range11="+ds.getRange11()+","
                    + "range12="+ds.getRange12()+" "
                    + "WHERE id="+ds.getId();
            stmt = con.prepareStatement(query,Statement.RETURN_GENERATED_KEYS);
            stmt.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(UnitModel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public ArrayList<Kabupaten> getKabupaten() {
        ArrayList<Kabupaten> ap = new ArrayList<Kabupaten>();
        
        Statement stmt = null;
        try {
            stmt = con.createStatement();
            String query="SELECT * FROM kabupaten";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                Kabupaten p = new Kabupaten(rs.getInt("id"), rs.getString("nama"),rs.getInt("id_provinsi"));
                ap.add(p);
            }
        } catch (SQLException ex) {
            Logger.getLogger(UnitModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ap;
    }
}
